class PerformanceSqlMap(object):
    FIND_BY_USER_ID = "select base_record,add_record,sub_record,count_record from aoa_performance_pool where user_id={user_id}"
    FIND_ALL_CLASSIFY = 'SELECT s.status_id,s.sort_value,s.status_name FROM aoa_status_list s where s.status_model="aoa_performance" ORDER BY s.sort_value'
    list_add_or_sub  = "select t3.create_time,t3.author as author_id,t1.content,t1.record,t2.real_name as author_name from aoa_performance_record as t3 inner join aoa_performance as t1 on t1.id=t3.performance_id inner join aoa_user as t2 on t3.author=t2.user_id where t3.user_id={user_id} and t1.add_or_sub={add_or_sub}"
    list_my  = "select t1.add_or_sub,t4.status_name,t3.create_time,t3.author as author_id,t1.content,t1.record,t2.real_name as author_name from aoa_performance_record as t3 inner join aoa_performance as t1 on t1.id=t3.performance_id inner join aoa_user as t2 on t3.author=t2.user_id inner join aoa_status_list as t4 on t1.classify=t4.status_id where t3.user_id={user_id}"
    list_other  = "select t1.add_or_sub,t4.status_name,t3.create_time,t1.content,t1.record,t2.real_name,t3.user_id from aoa_performance_record as t3 inner join aoa_performance as t1 on t1.id=t3.performance_id inner join aoa_user as t2 on t3.user_id=t2.user_id inner join aoa_status_list as t4 on t1.classify=t4.status_id where t3.author={user_id}"
    subordinates_performance_all = "select t1.real_name as subordinate_name,t1.user_id as subordinate_id,t2.count_record,t2.add_record,t2.sub_record,t4.role_name,h.jobnumber from aoa_user as t1 left join aoa_humanres as h on t1.user_id=h.user_id inner join aoa_performance_pool as t2 on t1.user_id=t2.user_id inner join aoa_dept as t3 on t3.dept_id=t1.dept_id inner join aoa_role_ as t4 on t4.role_id=t1.role_id where t1.user_id!={user_id}"
    subordinates_performance_sub = "select t1.real_name as subordinate_name,t1.user_id as subordinate_id,t2.count_record,t2.add_record,t2.sub_record,t4.role_name,h.jobnumber from aoa_user as t1 left join aoa_humanres as h on t1.user_id=h.jobnumber inner join aoa_performance_pool as t2 on t1.user_id=t2.user_id inner join aoa_dept as t3 on t3.dept_id=t1.dept_id inner join aoa_role_ as t4 on t4.role_id=t1.role_id where t4.role_value>{my_role} and t1.dept_id=(select dept_id from aoa_user where user_id={user_id})"
    subordinates_add_or_sub = "insert into aoa_performance_record(author,performance_id,user_id,create_time) values ({father_id},{performance_id},{user_id},now())"
    subordinates_pool_check = "select add_or_sub,record,is_active from aoa_performance where id={performance_id}"
    subordinates_pool_update1 = "update aoa_performance_pool set add_record=add_record+{record},count_record=count_record+{record} where user_id={user_id}"
    subordinates_pool_update2 = "update aoa_performance_pool set sub_record=sub_record+{record},count_record=count_record-{record} where user_id={user_id}"
    performance_appraisal = "select t1.id as performance_id,t1.record,t1.is_active,t1.content,t1.add_or_sub,t2.status_name from aoa_performance as t1 inner join aoa_status_list as t2 on t1.classify=t2.status_id"
    performance_create = "insert into aoa_performance(add_or_sub,content,is_active,record,classify,dept_id) values ({add_or_sub},'{content}',{is_active},{record},{classify},{dept_id})"
    performance_edit = "update aoa_performance set add_or_sub={add_or_sub},content='{content}',aoa_performance.is_active={is_active},record={record},classify={classify} where id={id}"
    performance_rank = "select t1.user_id,t2.real_name,t1.add_record,t1.sub_record,t1.count_record from aoa_performance_pool as t1 inner join aoa_user as t2 on t1.user_id=t2.user_id order by t1.count_record desc limit 0,10"
    FIND_ROLE = "select aoa_role_.role_value from aoa_user inner join aoa_role_ on aoa_user.role_id = aoa_role_.role_id where user_id={user_id}"
    GET_ID = "select @@IDENTITY as id"

    FILTER_PARAMS = {
        "query_str": "t1.content like '%{query_str}%'",
        "query_str_sub": "t1.real_name like '%{query_str_sub}%'",
        "status_id":"t1.classify={status_id}",
        "add_or_sub":"t1.add_or_sub={add_or_sub}"
    }